using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NCT.Framework;
using System.Data;
using System.Data.Common;
using NCT.Framework.Database;

namespace VISS.API.Users {
	class UserAdminDA {

        private const string SP_GetWithConditionAndPaging = "dbo.UserAdmin_GetWithConditionAndPaging";
		private const string SP_GetByUserKey = "dbo.UserAdmin_GetByUserKey";
        private const string SP_GetByEmail = "dbo.UserAdmin_GetByEmail";
        private const string SP_SearchByEmailWithPaging = "dbo.UserAdmin_SearchByEmailWithPaging";
        private const string SP_SearchByMobiPhoneWithPaging = "dbo.UserAdmin_SearchByMobiPhoneWithPaging";
        private const string SP_CountWithCondition = "dbo.UserAdmin_CountWithCondition";
        private const string SP_CountSearchByEmail = "dbo.UserAdmin_CountSearchByEmail";
        private const string SP_CountSearchByMobiPhone = "dbo.UserAdmin_CountSearchByMobiPhone";

		public static UserAdminEnt CreateFromReader(IDataReader reader) {
            UserAdminEnt item = new UserAdminEnt();
			item.UserKey = ConvertUtils.ToString(reader["UserKey"]);
			item.ID = ConvertUtils.ToLong(reader["ID"]);
			item.Email = ConvertUtils.ToString(reader["Email"]);
			item.Password = ConvertUtils.ToString(reader["Password"]);
			item.Status = (EUserStatus)ConvertUtils.ToShort(reader["Status"]);
			item.SLink = ConvertUtils.ToString(reader["SLink"]);
			item.SLinkExpiredDate = ConvertUtils.ToDateTime(reader["SLinkExpiredDate"]);
			item.DateCreated = ConvertUtils.ToDateTime(reader["DateCreated"]);
			item.LastLogin = ConvertUtils.ToDateTime(reader["LastLogin"]);
			item.DateActivated = ConvertUtils.ToDateTime(reader["DateActivated"]);
			item.DateModified = ConvertUtils.ToDateTime(reader["DateModified"]);
            item.AvatarFileName = ConvertUtils.ToString(reader["AvatarFileName"]);
            item.AvatarImageStoreID = ConvertUtils.ToInt(reader["AvatarImageStoreID"]);
            item.AvatarStatus = (EUserAvatarStatus)ConvertUtils.ToByte(reader["AvatarStatus"]);
            item.Name = ConvertUtils.ToString(reader["Name"]);
            item.MobiPhone = ConvertUtils.ToString(reader["MobiPhone"]);
            item.Gender = (EUserGender)ConvertUtils.ToByte(reader["Gender"]);
            item.Blast = ConvertUtils.ToString(reader["Blast"]);
            item.ReportedTime = ConvertUtils.ToShort(reader["ReportedTime"]);
            return item;
		}

        public static List<UserAdminEnt> SearchByEmailWithPaging(string emailTemplate, long startRow, long endRow, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            List<UserAdminEnt> result = new List<UserAdminEnt>();

            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_SearchByEmailWithPaging)) {
                db.AddParameter(cmd, "EmailTemplate", emailTemplate);
                db.AddParameter(cmd, "StartRow", startRow);
                db.AddParameter(cmd, "EndRow", endRow);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    while (reader.Read()) {
                        result.Add(CreateFromReader(reader));
                    }
                }
            }
            return result;
        }

        public static List<UserAdminEnt> SearchByMobiPhoneWithPaging(string mobiPhoneTemplate, long startRow, long endRow, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            List<UserAdminEnt> result = new List<UserAdminEnt>();

            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_SearchByMobiPhoneWithPaging)) {
                db.AddParameter(cmd, "MobiPhoneTemplate", mobiPhoneTemplate);
                db.AddParameter(cmd, "StartRow", startRow);
                db.AddParameter(cmd, "EndRow", endRow);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    while (reader.Read()) {
                        result.Add(CreateFromReader(reader));
                    }
                }
            }
            return result;
        }

        public static List<UserAdminEnt> GetWithConditionAndPaging(EUserGender gender, EUserStatus status, bool haveAvatar, long startRow, long endRow, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            List<UserAdminEnt> result = new List<UserAdminEnt>();
            string whereClause = BuildWhereClause(gender, status, haveAvatar);

            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_GetWithConditionAndPaging)) {
                db.AddParameter(cmd, "WhereClause", whereClause);
                db.AddParameter(cmd, "StartRow", startRow);
                db.AddParameter(cmd, "EndRow", endRow);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    while (reader.Read()) {
                        result.Add(CreateFromReader(reader));
                    }
                }
            }
            return result;
        }

        public static UserAdminEnt GetByUserKey(string userKey, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            UserAdminEnt result = null;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_GetByUserKey)) {
                db.AddParameter(cmd, "UserKey", userKey);
				using (IDataReader reader = db.ExecuteReader(cmd)) {
					if (reader.Read()) {
						result = CreateFromReader(reader);
					}
				}
			}
			return result;
		}

        public static UserAdminEnt GetByEmail(string email, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            UserAdminEnt result = null;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_GetByEmail)) {
                db.AddParameter(cmd, "Email", email);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    if (reader.Read()) {
                        result = CreateFromReader(reader);
                    }
                }
            }
            return result;
        }

        public static long CountSearchByEmail(string emailTemplate, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            long result = 0;

            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_CountSearchByEmail)) {
                db.AddParameter(cmd, "EmailTemplate", emailTemplate);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    if (reader.Read()) {
                        result = ConvertUtils.ToLong(reader["Count"]);
                    }
                }
            }
            return result;
        }


        public static long CountSearchByMobiPhone(string mobiPhoneTemplate, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            long result = 0;

            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_CountSearchByMobiPhone)) {
                db.AddParameter(cmd, "MobiPhoneTemplate", mobiPhoneTemplate);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    if (reader.Read()) {
                        result = ConvertUtils.ToLong(reader["Count"]);
                    }
                }
            }
            return result;
        }

        public static long CountWithCondition(EUserGender gender, EUserStatus status, bool haveAvatar, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            long result = 0;
            string whereClause = BuildWhereClause(gender, status, haveAvatar);

            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_CountWithCondition)) {
                db.AddParameter(cmd, "WhereClause", whereClause);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    if (reader.Read()) {
                        result = ConvertUtils.ToLong(reader["Count"]);
                    }
                }
            }
            return result;
        }

        private static string BuildWhereClause(EUserGender gender, EUserStatus status, bool haveAvatar) {
            string whereClause = "Where ";
            if (gender != EUserGender.Unknown) {
                whereClause += "UserGeneralProfile.Gender = " + ((byte)gender).ToString() + " and ";
            }
            switch ((short)status) {
                case (short)EUserStatus.NotActived:
                    whereClause += "u.Status = " + (short)EUserStatus.NotActived + " and ";
                    break;
                case (short)EUserStatus.Actived:
                    whereClause += "u.Status = " + (short)EUserStatus.Actived + " and ";
                    break;
                case (short)EUserStatus.Verified:
                    whereClause += "u.Status & " + (short)EUserStatus.Verified + "=" + (short)EUserStatus.Verified + " and ";
                    break;
                case -(short)EUserStatus.Verified:
                    whereClause += "u.Status & " + (short)EUserStatus.Verified + "<>" + (short)EUserStatus.Verified + " and ";
                    break;
                case (short)EUserStatus.Disabled:
                    whereClause += "u.Status & " + (short)EUserStatus.Disabled + "=" + (short)EUserStatus.Disabled + " and ";
                    break;
            }
            if (haveAvatar) {
                whereClause += "Exists (Select UserKey From UserAvatar Where u.UserKey = UserAvatar.UserKey) And ";
            }
            whereClause += "1 = 1";
            return whereClause;
        }
	}
}